Sun King Expansion Opportunities¶

This map has multipe layers: one for potential customers (colorful) and others for areas we already have covered (dark, hiding the colorful layer below). By stacking these layers, we can see expansion opportunities: areas with potential customers where we haven't sold yet.

To toggle the layers, use the layers button in the upper left corner.

  • Potential customers: (colorful)
  • Recent Sales: (dark) designed to hide the layers behind it
  • Active Agents: (dark) designed to hide the layers behind it
  • Counties: (outlines)

With Sales data on top (in black) hiding the potential customer layer below (colorful), what remains are expansion opportunities: areas of high potential customer density where we have yet to sell many products.

Clicking on an agent shows the agent's info, and clicking elsewhere on the map gives a google maps pin of that location.

In [228]:
mymap #show map
Out[228]:
Make this Notebook Trusted to load map: File -> Trust Notebook
In [6]:
# Population Data:

# Energy Access Explorer 
# https://www.energyaccessexplorer.org/tool/s/
#   --> Tool
#       --> Country: Kenya
#           --> Geography Kenya 
#               --> Expansion of Clean Energy Markets
#                   --> click menu (top right)
#                       * remove mini-grids
#                       * remove lower bound on distance to distribution lines
#                       * remove lower bound on poverty
#
# multi-criteria analysis described here:
# https://files.wri.org/d8/s3fs-public/energy-access-explorer-data-and-methods.pdf
In [178]:
# sales_minimum_per_agent = 50      # show agents with at least this many sales
# sql2 = f"""
#     SELECT base.angaza_id as agent,
#            base.country,
#            base.latitude ::DECIMAL(6,4) as base_lat, 
#            base.longitude::DECIMAL(7,4) as base_long,
#            COUNT(accounts.angaza_id)    as count_sales
#       FROM kazi_prod.fse_base_location  as base
#  LEFT JOIN easybuy.accounts_final_pdt   AS accounts
#         ON base.angaza_id = accounts.registering_user_angaza_id
#      WHERE accounts.registration_date_utc::DATETIME  > GETDATE() - INTERVAL '{sales_interval}'
#         -- todo
#          # todo: active agents filter
#        AND base.country = '{country.upper()}'
#   GROUP BY agent, base.country, base_lat, base_long
#     HAVING count_sales >= '{sales_minimum_per_agent}'
#      LIMIT 100000
#        """

con = con
sql2 = f""" -- this query occasionally returns zero rows, proximal problem seems to be in db
      WITH agent_status_pdt AS (SELECT country,
                                       responsible_user_angaza_id,
                                       area_derived as area,
                                       MAX(registration_date_utc) as last_sale
                                  FROM easybuy.accounts_final_pdt as accounts
                                 WHERE country = '{country}'
                              GROUP BY 1,2,3),
    agents as(
         SELECT base.angaza_id as agent,
                base.country,
                base.latitude ::DECIMAL(6,2) AS base_lat, 
                base.longitude::DECIMAL(7,2) AS base_long,
                base_lat || base_long as lat_long_string,
                ROW_NUMBER() OVER (PARTITION BY lat_long_string ORDER BY lat_long_string) as row_num,
                agent_status_pdt.area as area,
                users_all.first_name || ' ' || users_all.last_name as name,
                users_all.primary_phone as phone

           FROM easybuy.users AS users_all
      LEFT JOIN kazi_prod.fse_base_location  as base
             ON users_all.angaza_id = base.angaza_id
      LEFT JOIN agent_status_pdt
             ON users_all.angaza_id = agent_status_pdt.responsible_user_angaza_id
          WHERE DATEDIFF('DAYS',last_sale, CURRENT_DATE) < 60 -- is_active_agent
            AND agent IS NOT NULL
            AND agent_status_pdt.country = '{country}'
            )
    SELECT * from agents
     WHERE row_num = 1 -- gps points hidden below other gps points are invisible and slow down / cause folium map to hang
        
            """
df_base2 = pd.read_sql(sql2, con)
df_base2.set_index('agent', inplace=True)
print("\n\n\n# of agents: ", df_base2.shape[0], "\n\n\n")
df_base2.head(2)


# of agents:  5730 



Out[178]:
country base_lat base_long lat_long_string row_num area name phone
agent
US084361 KENYA -0.03 34.02 -0.0334.02 1 Bondo Auma Jane +254796716660
US084247 KENYA -0.08 34.23 -0.0834.23 1 Bondo anne ongaro +254759626562
In [193]:
# Create a raster with 5km x 5km cells
res = 5000  # Resolution in meters
In [131]:
cutoff_sales_num = 50

improvements

agreed:

  • add gradiant [done]
  • add electrification, income [done]
  • update colormap [done]

dan's ideas:

  • split by product
  • add notes aobut open source info [done]
  • check if layers' crs's match
  • refine active agents (selling recently, recenlty addes but haven't sold yet, etc.)
  • check if all agents sell all products

  • subcounties and sub-sub counties:

  • https://data.humdata.org/dataset/administrative-wards-in-kenya-1450
  • https://data.amerigeoss.org/dataset/kenya-sub-counties